
--1）编写视图实现查询出所有银行卡账户信息，显示卡号，身份证，姓名，余额。
go
create view V_newab(卡号,身份证,姓名,余额)
as
select BankCard.CardNo,AccountInfo.AccountCode,AccountInfo.RealName,BankCard.CardMoney from BankCard left join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
go
--2）行转列常用做法: group by + sum(case when) /+count(case when)  数据分析+ over (paritition by, order by)
insert into record values
(1991,1,1.1),
(1991,2,1.2),
(1991,3,1.3),
(1991,4,1.4),
(1992,1,2.1),
(1992,2,2.2),
(1992,3,2.3),
(1992,4,2.4)
select * from record
go
create view V_RErecord(year,m1,m2,m3,m4)
as
 select * from record 
 pivot (sum(amount) for month in ([1],[2],[3],[4])) as RErecord
 go
 select * from V_RErecord
 --3)列转行
 create table sc (
 name varchar(10),
 english int ,
 maths int ,
 music int
  )
insert into sc values ('Jim',90,88,99)
 select name,'english' subject,english as score from sc 
 union
  select name,'maths' subject,maths as score from sc 
  union
   select name,'music' subject,music as score from sc 